﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
using System.Data.SqlClient;
using Entities;

namespace DataAccessLayer
{
    public class DieuNhayDAL : DBConnection
    {
        private DataSet ds;
        private MySqlDataAdapter daDN;

        public DieuNhayDAL()
        {
        }

        public List<DieuNhayEntity> getallDieuNhay()
        {
            List<DieuNhayEntity> lstDN = null;
            try
            {
                lstDN = new List<DieuNhayEntity>();
                string sql = "SELECT * FROM dieu_nhay";
                if (con.State != ConnectionState.Open)
                    con.Open();
                MySqlDataReader reader = new MySqlCommand(sql, con).ExecuteReader();
                while (reader.Read())
                {
                    DieuNhayEntity dn = new DieuNhayEntity();
                    dn.MaDieuNhay = reader["MaHocVien"].ToString();
                    dn.TenDieuNhay = reader["HoTen"].ToString();
                    dn.MotaDieuNhay = reader["SDT"].ToString();
                    lstDN.Add(dn);
                }
                reader.Close();
            }
            catch (Exception)
            {
                throw;
            }
            return lstDN;
        }

        public DieuNhayEntity getdataByMaDieuNhay(string maDN)
        {
            DieuNhayEntity dn = null;
            try
            {
                if (con.State != ConnectionState.Open)
                    con.Open();
                string sql = "SELECT * FROM dieu_nhay WHERE MaDieuNhay='" + maDN + "'";
                MySqlDataReader dr = new MySqlCommand(sql, con).ExecuteReader();
                if (dr.Read())
                {
                    dn = new DieuNhayEntity();
                    dn.MaDieuNhay = dr["MaDieuNhay"].ToString();
                    dn.TenDieuNhay = dr["TenDieuNhay"].ToString();
                    dn.MotaDieuNhay = dr["MoTa"].ToString();
                    dr.Close();
                }

            }
            catch (Exception)
            {
                throw;
            }
            return dn;
        }

        public int addDieuNhay(DieuNhayEntity dn)
        {
            try
            {
                if (con.State != ConnectionState.Open)
                    con.Open();
                string sql = "INSERT INTO dieu_nhay (MaDieuNhay, TenDieuNhay, MoTa) VALUES ('" + dn.MaDieuNhay + "','" + dn.TenDieuNhay + "','" + dn.MotaDieuNhay + "');";
                MySqlCommand cm = new MySqlCommand(sql, con);
                return cm.ExecuteNonQuery();
            }
            catch (Exception)
            {
                return 0;
            }
        }

        public bool deleteDieuNhay(string maDN)
        {
            bool kq = false;
            try
            {
                string sql = "DELETE FROM dieu_nhay WHERE MaDieuNhay='" + maDN + "'";
                MySqlCommand cm = new MySqlCommand(sql, con);
                kq = cm.ExecuteNonQuery() > 0 ? true : false;
            }
            catch (Exception)
            {
                throw;
            }
            return kq;
        }

        public void updateDieuNhay()
        {
            MySqlCommandBuilder bd = new MySqlCommandBuilder(daDN);
            daDN.Update(ds, "hoc_vien");
        }

        public void updateDieuNhay(DieuNhayEntity dn)
        {
            try
            {
                getallDieuNhay();
                DataTable dt = ds.Tables["dieu_nhay"];
                DataRow dr = dt.NewRow();
                dr["TenDieuNhay"] = dn.TenDieuNhay;
                dr["MoTa"] = dn.MotaDieuNhay;
                dt.Rows.Add(dr);
                updateDieuNhay();
            }
            catch (Exception)
            {
                throw;
            }
        }
    }
}
